package cn.DB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.entity.goods;

public class DBProcess {

	static String ADDSQL = "insert into goods(goodid,goodname,"
			+ "goodproperty,goodprice,goodqty) values(?,?,?,?,?)";

	static String UPDATESQL = "update goods set goodid=?,goodname=?"
			+ ",goodproperty=?,goodprice=?,goodqty=? where goodid=?";

	static String LISTSQL = "select * from goods";

	static String gETONESQL = "select * from goods where goodid=?";

	static String DELETESQL = "delete from goods where goodid=?";

	private Connection con;
	private Statement stat;
	private PreparedStatement pstat;
	private ResultSet res;

	private void getCon() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/gouwuche?characterEncoding=UTF-8",
				"root", "");
	}

	public ArrayList<goods> getgoodsList() throws ClassNotFoundException,
			SQLException {
		ArrayList<goods> goodslist = new ArrayList<goods>();
		if (con == null) {
			getCon();
		}
		getStat();
		res = stat.executeQuery(LISTSQL);
		while (res.next()) {
			goodslist.add(getgoods(res));
		}

		closeDB();
		return goodslist;
	}

	public goods getgoodsById(String goodid) throws ClassNotFoundException,
			SQLException {
		goods goo = null;
		if (con == null) {
			getCon();
		}
		getPstat(gETONESQL);
		pstat.setString(1, goodid);
		res = pstat.executeQuery();
		if (res.next()) {
			goo = getgoods(res);
		}

		closeDB();
		return goo;
	}

	public void savegoods(goods goo, boolean add, String oldId)
			throws ClassNotFoundException, SQLException {
		if (con == null) {
			getCon();
		}

		if (add) {
			getPstat(ADDSQL);
		} else {
			getPstat(UPDATESQL);
			pstat.setString(6, oldId);
		}

		pstat.setString(1, goo.getgoodid());
		pstat.setString(2, goo.getgoodname());
		pstat.setString(3, goo.getgoodproperty());
		pstat.setFloat(4, goo.getgoodprice());
		pstat.setInt(5, goo.getgoodqty());
		// 执行语句
		pstat.executeUpdate();

		closeDB();
		// System.out.print("添加成功");
	}

	public void deleteById(String goodid) throws ClassNotFoundException,
			SQLException {
		if (con == null) {
			getCon();
		}

		getPstat(DELETESQL);
		pstat.setString(1, goodid);

		pstat.executeUpdate();

		closeDB();
	}

	private goods getgoods(ResultSet res) throws SQLException {
		goods goo = new goods();
		goo.setgoodid(res.getString("goodid"));
		goo.setgoodname(res.getString("goodname"));
		goo.setgoodprice(res.getFloat("goodprice"));
		goo.setgoodproperty(res.getString("goodproperty"));
		goo.setgoodqty(res.getInt("goodqty"));
		return goo;
	}

	private void getStat() throws SQLException {
		stat = con.createStatement();
	}

	private void getPstat(String sql) throws SQLException {
		pstat = con.prepareStatement(sql);
	}

	private void closeDB() {
		try {
			if (res != null) {
				res.close();
			}
			if (stat != null) {
				stat.close();
			}
			if (pstat != null) {
				pstat.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
